import * as DocumentPicker from "expo-document-picker";
import * as FileSystem from "expo-file-system"; // Import FileSystem
// 增加对于用户媒体库的控制
import * as MediaLibrary from "expo-media-library";
import * as SQLite from "expo-sqlite";

import {
    Alert,
    Button,
    FlatList,
    StyleSheet,
    Text,
    TextInput,
    View,
} from "react-native";
import React, { useEffect, useState } from "react";

/**
 * 选择当前的存放文件的位置：
 */
const selectSaveDirectory = async () => {
    try {
        const res = await DocumentPicker.getDocumentAsync({
            type: "*/*",
            // 若要允许 expo-file-system 在选取文件后立即读取文件，
            // 需要确保该 copyToCacheDirectory 选项设置为 true 。
            copyToCacheDirectory: true,
        });
        console.log(JSON.stringify(res));
        alert(JSON.stringify(res));
        // 数据格式内容：{"assets":[{"mimeType":"application/epub+zip","uri":"content://com.android.providers.media.documents/document/document%3A728136","name":"半小时漫画中国哲学史（其实是一本严谨的极简中国哲学史！漫画科普开创者混子哥新作！全网粉丝1300万！）.epub","size":28176868}],"canceled":false}
        if (res?.assets[0]?.uri) {
            const directory = res?.assets[0]?.uri.substring(
                0,
                res?.assets[0]?.uri.lastIndexOf("/")
            );

            setSaveDirectory(directory);
            Alert.alert("Save directory selected", directory);
        } else {
            console.log("Document picking was cancelled");
            Alert.alert("Document picking was cancelled");
        }
    } catch (err) {
        console.error("Failed to pick directory", err);
    }
};

/**
 * 增加一个移动文件到公共目录
 */




/**
 * 随便选择当前的手机公共目录的权限
 */




/**
 * sqlite数据库初始化的创建
 * initDatabase
 * 创建数据库，返回当前的数据库的名称和数据库汇总
 */
const initDatabase = async () => {
    // 这里默认初始化的时候，创建一个名称为 test.db 的数据库文件，如果没有创建出来
    const db = await SQLite.openDatabaseAsync("test.db"); // 确保使用正确的数据库文件名
    // 打印数据库文件路径
    console.log(
        "Database file path:",
        `${FileSystem.documentDirectory}SQLite/test.db`
    );

    // 执行 SQL 语句
    await db.execAsync(`
      PRAGMA journal_mode = WAL;
      CREATE TABLE IF NOT EXISTS test (id INTEGER PRIMARY KEY NOT NULL, value TEXT NOT NULL, intValue INTEGER);
      INSERT OR IGNORE INTO test (value, intValue) VALUES ('test1', 123);
      INSERT OR IGNORE INTO test (value, intValue) VALUES ('test2', 456);
      INSERT OR IGNORE INTO test (value, intValue) VALUES ('test3', 789);
    `);

    // 获取所有数据
    const allRows = await db.getAllAsync("SELECT * FROM test");
    // setData(allRows);
    return allRows
};

/**
 * 数据库sqlite的增删查改
 */


/**
 * 数据库新增数据
 * @param dbName 数据库的名称
 * @param newValue 新增的数据
 * @param newIntValue 新增的数据
 */
const insertData = async (dbName: string, newValue: any, newIntValue: any) => {
    if (newValue && newIntValue) {
        const db = await SQLite.openDatabaseAsync(dbName); // 确保使用正确的数据库文件名
        await db.runAsync(
            "INSERT INTO test (value, intValue) VALUES (?, ?)",
            newValue,
            parseInt(newIntValue)
        );

        await refreshData(dbName);
        // 业务代码到时候，将插入的数据清空，准备下一次插入的准备
        // setNewValue("");
        // setNewIntValue("");
        Alert.alert("Data inserted successfully");
    } else {
        Alert.alert("Please provide both value and intValue");
    }
};

/**
 * sqlite数据库删除数据
 */
const deleteData = async (dbName: string, data: any[]) => {
    if (data.length > 0) {
        const db = await SQLite.openDatabaseAsync(dbName); // 确保使用正确的数据库文件名
        await db.runAsync("DELETE FROM test WHERE id = ?", [data[0].id]);
        await refreshData(dbName);
        Alert.alert("Data deleted successfully");
    } else {
        Alert.alert("No data to delete");
    }
};

/**
 * sqlite数据库查询数据
 */
const queryData = async (queryId: string, dbName: string) => {
    if (queryId) {
        const db = await SQLite.openDatabaseAsync(dbName); // 确保使用正确的数据库文件名
        const result = await db.getFirstAsync("SELECT * FROM test WHERE id = ?", [
            parseInt(queryId),
        ]);
        if (result) {
            Alert.alert(
                "Query Result",
                `ID: ${result.id}\nValue: ${result.value}\nIntValue: ${result.intValue}`
            );
        } else {
            Alert.alert("No data found for the given ID");
        }
    } else {
        Alert.alert("Please provide an ID to query");
    }
};

/**
 * sqlite数据库刷新数据
 */
const refreshData = async (dbName: string) => {
    const db = await SQLite.openDatabaseAsync(dbName); // 确保使用正确的数据库文件名
    const allRows = await db.getAllAsync("SELECT * FROM test");
    return allRows

};
/**
 * 导出sqlite数据库导公共目录Picture
 * dbFilePath 当前的数据库的存放位置
 */
// 导出 SQLite 数据库
const exportCommonDatabase = async (dbName: string) => {
    try {
        // 获取 SQLite 数据库文件路径
        const dbFilePath = `${FileSystem.documentDirectory}SQLite/${dbName}`;

        // 读取数据库文件内容
        const fileData = await FileSystem.readAsStringAsync(dbFilePath, {
            encoding: FileSystem.EncodingType.Base64,
        });

        // 创建导出文件路径
        const exportPath = `${FileSystem.documentDirectory}/SQLite/test_backup.db`;

        // 写入导出文件
        await FileSystem.writeAsStringAsync(exportPath, fileData, {
            encoding: FileSystem.EncodingType.Base64,
        });

        Alert.alert("Success", `Database exported successfully to ${exportPath}`);
    } catch (error) {
        Alert.alert("Error", "Failed to export the database: " + error.message);
    }
};



/**
 * 导出sqlite数据库导公共目录Document
 */





export {
    exportCommonDatabase,
    selectSaveDirectory,
    initDatabase,
    refreshData,
    queryData,
    deleteData,
}